﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq.Expressions;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data.Common;
using Snowflake.Entity;

namespace Snowflake.Gateway
{
    /// <summary>
    /// Gateway between database and entity object representation
    /// </summary>
    public sealed class EntityGateway : IDisposable
    {
        private IDbConnection _DbConnection;
        private IDbTransaction _DbTransaction;
        private bool _AcceptChangesDuringSave;
        private int _QueryTimeout;

        /// <summary>
        /// Initialize the object of class EntityGateway with specified database connection
        /// </summary>
        /// <param name="dbConnection">The data base connection that will be used</param>
        public EntityGateway(IDbConnection dbConnection)
        {
            this._DbConnection = dbConnection;
            this._AcceptChangesDuringSave = true;
            this._QueryTimeout = 30;
        }

        /// <summary>
        /// Gets or sets to accept changes of all entity objects after save
        /// </summary>
        public bool AcceptChangesDuringSave
        {
            get { return this._AcceptChangesDuringSave; }
            set { this._AcceptChangesDuringSave = value; }
        }

        /// <summary>
        /// Timeout of EntityGateway' queries 
        /// </summary>
        public int QueryTimeout
        {
            get { return this._QueryTimeout; }
            set { this._QueryTimeout = value; }
        }

        #region TestConnection
        /// <summary>
        /// Test Connection to Server
        /// </summary>
        /// <returns>True/False</returns>
        public bool TestConnection()
        {
            try
            {
                this._DbConnection.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                this._DbConnection.Close();
            }

        }
        #endregion

        #region Transaction Management
        /// <summary>
        /// Begin Transaction
        /// </summary>
        public void BeginTransaction()
        {
            this.BeginTransaction(IsolationLevel.ReadCommitted);
        }

        /// <summary>
        /// Begin Transaction
        /// </summary>
        /// <param name="isolationLevel">Isolation Level</param>
        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            this.OpenConnection();

            this._DbTransaction = this._DbConnection.BeginTransaction(isolationLevel);
        }

        /// <summary>
        /// Roll Back Transaction
        /// </summary>
        public void RollBackTransaction()
        {
            this._DbTransaction.Rollback();

            this.EndTransaction();

        }
        /// <summary>
        /// Commit Transaction
        /// </summary>
        public void CommitTransaction()
        {
            this._DbTransaction.Commit();

            this.EndTransaction();
        }
        /// <summary>
        /// Close SqlConnection and sets mSqlTransaction to NULL value
        /// </summary>
        private void EndTransaction()
        {
            if (this._DbTransaction != null)
            {
                this._DbTransaction.Dispose();
                this._DbTransaction = null;
            }

            this.CloseConnection();
        }
        #endregion

        #region Connection Management
        /// <summary>
        /// Open SQL Connection
        /// </summary>
        private void OpenConnection()
        {
            if (this._DbTransaction == null)
            {
                ConnectionState state = this._DbConnection.State;

                if (state != ConnectionState.Open && state != ConnectionState.Connecting)
                    this._DbConnection.Open();
            }
        }

        /// <summary>
        /// Close SQL Connection
        /// </summary>
        private void CloseConnection()
        {
            if (this._DbTransaction == null)
            {
                ConnectionState state = this._DbConnection.State;

                if (state != ConnectionState.Closed)
                    this._DbConnection.Close();
            }
        }
        #endregion


        private DataContext CreateDataContext(bool objectTrackingEnabled)
        {
            DataContext dataContext = new DataContext(this._DbConnection);

            if (this._DbTransaction != null)
                dataContext.Transaction = this._DbTransaction as DbTransaction;

            //dataContext.Log = new DebuggerWriter();
            dataContext.CommandTimeout = this._QueryTimeout;
            dataContext.ObjectTrackingEnabled = objectTrackingEnabled;
            return dataContext;
        }

        /// <summary>
        /// Gets table representation of the entity object's table from database
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity object</typeparam>
        /// <returns>Table of entity object from the database</returns>
        public Table<TEntity> GetTable<TEntity>() where TEntity : EntityObject<TEntity>, new()
        {
            using (DataContext dataContext = this.CreateDataContext(false))
            {
                Table<TEntity> table = dataContext.GetTable<TEntity>();
                return table;
            }
        }

        /// <summary>
        /// Executes IQueryable query of EntityObjects
        /// </summary>
        /// <typeparam name="TEntity">The type of the EntityObjects that will retrieved</typeparam>
        /// <param name="query">The query that will be executed</param>
        /// <returns>Results from the query as EntityList</returns>
        public EntityList<TEntity> ExecuteQuery<TEntity>(IQueryable<TEntity> query) where TEntity : EntityObject<TEntity>, new()
        {
            if (query == null)
                throw new ArgumentNullException("Query cannot be null.");

            if (!(query.Expression is MethodCallExpression))
                throw new ArgumentException("Query is not MethodCallExpression");

            MethodCallExpression expression = query.Expression as MethodCallExpression;

            using (DataContext dataContext = this.CreateDataContext(false))
            {
                QueryTranslator<TEntity> queryTranslator = new QueryTranslator<TEntity>(dataContext, query.Expression as MethodCallExpression);
                IQueryable<TEntity> translatedQuery = queryTranslator.Translate();

                IList<TEntity> resultList = translatedQuery.ToList();
                return new EntityList<TEntity>(resultList);
            }
        }


        /// <summary>
        /// Executes EntityQuery
        /// </summary>
        /// <typeparam name="TEntity">The type of the entityquery</typeparam>
        /// <param name="query">The entity query that will be executed</param>
        /// <returns>Affected rows</returns>
        public int ExecuteNonQuery<TEntity>(EntityQuery<TEntity> query) where TEntity : EntityObject<TEntity>, new()
        {
            string tableName = null;

            IQueryable<TEntity> theQuery = (query as EntityQuery<TEntity>).SelectQuery;
            DbCommand command = this.GetQueryCommand<TEntity>(theQuery, out tableName);

            string preDefinedCommandText = query.TranslateSQLQuery(tableName, command.CommandText, command.Parameters);
            command.CommandText = preDefinedCommandText;

            try
            {
                this.OpenConnection();

                int affectedRecords = command.ExecuteNonQuery();
                return affectedRecords;
            }
            finally
            {
                this.CloseConnection();
            }
        }


        private DbCommand GetQueryCommand<TEntity>(IQueryable<TEntity> query, out string tableName) where TEntity : EntityObject<TEntity>, new()
        {
            if (query == null)
                throw new ArgumentNullException("Query cannot be null.");

            if (!(query.Expression is MethodCallExpression))
                throw new ArgumentException("Query is not MethodCallExpression");

            MethodCallExpression expression = query.Expression as MethodCallExpression;

            using (DataContext dataContext = this.CreateDataContext(false))
            {
                QueryTranslator<TEntity> queryTranslator = new QueryTranslator<TEntity>(dataContext, query.Expression as MethodCallExpression);

                tableName = queryTranslator.TableName;

                IQueryable<TEntity> translatedQuery = queryTranslator.Translate();
                DbCommand command = dataContext.GetCommand(translatedQuery);

                if (this._DbTransaction != null)
                    command.Transaction = this._DbTransaction as DbTransaction;

                command.CommandTimeout = this._QueryTimeout;
                return command;
            }
        }


        /// <summary>
        /// Load all records from table as EntityList
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity object that will loaded</typeparam>
        /// <returns>Loaded from the database EntityList of specified entity object</returns>
        public EntityList<TEntity> LoadAll<TEntity>() where TEntity : EntityObject<TEntity>, new()
        {
            return Load<TEntity>(null);
        }

        /// <summary>
        /// Load all records from table as EntityList
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity object that will loaded</typeparam>
        /// <typeparam name="TKey">The type of the field that will order by</typeparam>
        /// <param name="orderBy">OrderBy expression that is used to sort the result</param>
        /// <returns>Loaded from the database EntityList of specified entity object</returns>
        public EntityList<TEntity> LoadAll<TEntity, TKey>(Expression<Func<TEntity, TKey>> orderBy) where TEntity : EntityObject<TEntity>, new()
        {
            return Load<TEntity, TKey>(null, orderBy, OrderByDirection.Ascending);
        }


        /// <summary>
        /// Load all records from table as EntityList
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity object that will loaded</typeparam>
        /// <typeparam name="TKey">The type of the field that will order by</typeparam>
        /// <param name="orderBy">OrderBy expression that is used to sort the result</param>
        /// <param name="orderByDirection">Direction of order by clause</param>
        /// <returns>Loaded from the database EntityList of specified entity object</returns>
        public EntityList<TEntity> LoadAll<TEntity, TKey>(Expression<Func<TEntity, TKey>> orderBy, OrderByDirection orderByDirection) where TEntity : EntityObject<TEntity>, new()
        {
            return Load<TEntity, TKey>(null, orderBy, orderByDirection);
        }

        /// <summary>
        /// Loads EntityObject from that database as EntityList by specified where statement
        /// </summary>
        /// <typeparam name="TEntity">The type of entity objects that will be loaded</typeparam>
        /// <param name="whereExpression">Where expression that objects should obey</param>
        /// <returns>The EntityList of objects that obey the conditions</returns>
        public EntityList<TEntity> Load<TEntity>(Expression<Func<TEntity, bool>> whereExpression) where TEntity : EntityObject<TEntity>, new()
        {
            return Load<TEntity, object>(whereExpression, null, OrderByDirection.Ascending);
        }

        /// <summary>
        /// Loads EntityObject from that database as EntityList by specified where and orderby statement
        /// </summary>
        /// <typeparam name="TEntity">The type of entity objects that will be loaded</typeparam>
        ///<typeparam name="TKey">The type of the field that will order by</typeparam>
        /// <param name="whereExpression">Where expression that objects should obey</param>
        /// <param name="orderBy">OrderBy expression that is used to sort the result</param>
        /// <param name="orderByDirection">Specified the order of sorting</param>
        /// <returns>Sorted EntityList of objects that obey the conditions</returns>
        public EntityList<TEntity> Load<TEntity, TKey>(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, TKey>> orderBy, OrderByDirection orderByDirection) where TEntity : EntityObject<TEntity>, new()
        {
            using (DataContext dataContext = this.CreateDataContext(false))
            {
                Table<TEntity> table = dataContext.GetTable<TEntity>();

                var query = from item in table
                            select item;

                if (whereExpression != null)
                    query = query.Where(whereExpression);

                if (orderBy != null)
                {
                    if (orderByDirection == OrderByDirection.Ascending)
                        query = query.OrderBy(orderBy);
                    else if (orderByDirection == OrderByDirection.Descending)
                        query = query.OrderByDescending(orderBy);
                }

                IList<TEntity> resultList = query.ToList();
                return new EntityList<TEntity>(resultList);
            }
        }

        /// <summary>
        /// Load EntityObject by Primary Key
        /// </summary>
        /// <typeparam name="TEntity">The type of the EntityObject that will loaded</typeparam>
        /// <param name="pkKey">Value of primary key</param>
        /// <param name="pkKeys">Additinal values for the primary key if it contains more than one column</param>
        /// <returns>EntityObject that has the specified primary key</returns>
        public TEntity LoadByPrimaryKey<TEntity>(object pkKey, params object[] pkKeys) where TEntity : EntityObject<TEntity>, new()
        {
            if (pkKey == null || (pkKeys.Length > 0 && pkKeys.Contains(null)))
                throw new ArgumentNullException("Primary key cannot include null value.");

            List<object> primaryKeys = new List<object>();
            primaryKeys.Add(pkKey);
            primaryKeys.AddRange(pkKeys);

            TEntity entity = null;
            Type entityType = typeof(TEntity);

            using (DataContext dataContext = this.CreateDataContext(false))
            {
                Table<TEntity> table = dataContext.GetTable<TEntity>();

                MetaType metaEntityType = dataContext.Mapping.GetMetaType(entityType);

                var primaryKeyColumns = from pkColumn in metaEntityType.DataMembers
                                        where pkColumn.IsPrimaryKey
                                        select pkColumn;

                int pkColumnsCount = 0;

                if (primaryKeyColumns != null)
                    pkColumnsCount = primaryKeyColumns.Count();

                if (pkColumnsCount == 0)
                    throw new InvalidOperationException("Table doesn't have primary key");

                if (pkColumnsCount != primaryKeys.Count)
                    throw new InvalidOperationException("Primary key value doesn't match primary key columns.");

                ParameterExpression paramExpression = Expression.Parameter(entityType, "entity");

                BinaryExpression whereExpression = null;

                int index = 0;

                foreach (MetaDataMember pkColumn in primaryKeyColumns)
                {
                    object value = primaryKeys[index];
                    string columnName = pkColumn.Name;

                    if (value != null && value.GetType() != pkColumn.Type)
                    {
                        Type paramType = value.GetType();
                        string exceptionMsg = String.Format("The type '{0}' of parameter '{1}' is different than its column '{2}' type '{3}'", paramType, value, columnName, pkColumn.Type);
                        throw new InvalidOperationException(exceptionMsg);
                    }

                    BinaryExpression condition = Expression.Equal(Expression.Property(paramExpression, columnName), Expression.Constant(value));

                    if (whereExpression != null)
                        whereExpression = Expression.And(whereExpression, condition);
                    else
                        whereExpression = condition;

                    index++;
                }

                Expression<Func<TEntity, bool>> predicate = Expression.Lambda<Func<TEntity, bool>>(whereExpression, new ParameterExpression[] { paramExpression });
                entity = table.SingleOrDefault(predicate);

                if (entity != null)
                {
                    IList<TEntity> iternalList = new List<TEntity>();
                    iternalList.Add(entity);
                    EntityList<TEntity> entityList = new EntityList<TEntity>(iternalList);
                }
            }

            return entity;
        }

        /// <summary>
        /// Saves EntityList in the database
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity objects that will be saved</typeparam>
        /// <param name="entities">The EntityList of entity object for save</param>
        public void Save<TEntity>(EntityList<TEntity> entities) where TEntity : EntityObject<TEntity>, new()
        {
            Save<TEntity>(entities, ConflictMode.ContinueOnConflict);
        }

        /// <summary>
        /// Saves EntityList in the database with concrete concurrency mode
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity objects that will be saved.</typeparam>
        /// <param name="entities">The EntityList of entity object for save</param>
        /// <param name="conflictMode">Specifies when the concurrency conflict should be reported.</param>
        public void Save<TEntity>(EntityList<TEntity> entities, ConflictMode conflictMode) where TEntity : EntityObject<TEntity>, new()
        {
            if (entities == null)
                throw new ArgumentNullException("Parameter 'entities' cannot be null.");

            TEntity[] deleted = entities.GetChanges(EntityObjectState.Deleted);
            TEntity[] modified = entities.GetChanges(EntityObjectState.Modified);
            TEntity[] inserted = entities.GetChanges(EntityObjectState.Added);

            bool succeed = true;
            bool doSave = (deleted != null && deleted.Length > 0) || (modified != null && modified.Length > 0) || (inserted != null && inserted.Length > 0);

            if (!doSave)
                return;

            try
            {
                using (DataContext dataContext = this.CreateDataContext(true))
                {
                    Table<TEntity> table = dataContext.GetTable<TEntity>();

                    if (deleted != null && deleted.Length > 0)
                    {
                        table.AttachAll<TEntity>(deleted);
                        table.DeleteAllOnSubmit<TEntity>(deleted);
                    }

                    if (modified != null && modified.Length > 0)
                    {
                        foreach (TEntity obj in modified)
                        {
                            TEntity original = obj.OriginalObject as TEntity;
                            table.Attach(obj, original);
                        }
                    }

                    if (inserted != null && inserted.Length > 0)
                        table.InsertAllOnSubmit(inserted);

                    try
                    {
                        dataContext.SubmitChanges(conflictMode);
                    }
                    catch (ChangeConflictException ex)
                    {
                        throw new SaveConflictException("There are conflicted records.", ex, dataContext.ChangeConflicts);
                    }
                }
            }
            catch
            {
                succeed = false;
                throw;
            }
            finally
            {
                if (succeed && this._AcceptChangesDuringSave)
                    entities.AcceptChanges();
            }
        }

        /// <summary>
        /// Explicit dispose of the object
        /// </summary>
        public void Dispose()
        {
            if (this._DbTransaction != null)
            {
                this._DbTransaction.Commit();
                this._DbTransaction.Dispose();
                this._DbTransaction = null;
            }
        }
    }
}
